﻿Imports System.Data.OleDb

Module Mod_Auto_Access
    Public Function AutoID4Access(ByVal SQLStr As String, ByVal TblName As String) As Integer
        Try
            '   Dim sqlConn As String
            If acsconn.State = ConnectionState.Closed Then
                acsconn.Open()
            End If
            Dim MaxRow As Integer
            Dim objDataAdapter As New OleDb.OleDbDataAdapter
            Dim objDataset As New DataSet
            objDataAdapter = New OleDb.OleDbDataAdapter(SQLStr, acsconn)
            objDataAdapter.Fill(objDataset, TblName)
            'acsconn.Close()
            Dim NRow As Integer = objDataset.Tables(TblName).Rows.Count
            If NRow = 0 Then
                MaxRow = objDataset.Tables(TblName).Rows(0).Item(0)
            Else
                MaxRow = NRow
            End If
            Return MaxRow
        Catch ex As SystemException
            MsgBox(ex.Message)
        End Try
    End Function
    Public Sub LoadDataToLVDept(ByVal SQL As String, ByVal LV As ListView)
        Dim cn As New OleDbConnection
        Dim DA As OleDbDataAdapter
        Dim DS As New DataSet
        ' Dim dt As DataTable
        Dim dbCmd As OleDbCommand
        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\students.accdb"
        cn.Open()
        dbCmd = New OleDbCommand(SQL, cn)
        DA = New OleDbDataAdapter(dbCmd)
        DS = New DataSet
        DA.Fill(DS)
        LV.Clear()
        Dim j As Integer

        For i = 0 To DS.Tables(0).Rows.Count - 1
            With LV
                .Items.Add(DS.Tables(0).Rows(i)("DeptID").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("DeptName").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("DeptNote").ToString)
                '.Items(j).SubItems.Add(DS.Tables(0).Rows(i)("UserLogin").ToString)
                '.Items(j).SubItems.Add(DS.Tables(0).Rows(i)("Position").ToString)
            End With
            j = j + 1
        Next
        cn.Close()
        With LV
            .View = View.Details
            .GridLines = True
            .FullRowSelect = True
            .Columns.Add("លេខរៀង", 100, HorizontalAlignment.Center)
            .Columns.Add("ឈ្មោះ​ដេប៉ាតឺម៉ង់​​", 150, HorizontalAlignment.Center)
            .Columns.Add("កត់ចំនាំ", 0, HorizontalAlignment.Center)
            '.Columns.Add("ឈ្មោះ​ឡក់​អ៊ីន", 100, HorizontalAlignment.Center)
            '.Columns.Add("តូនាទី​", 200, HorizontalAlignment.Center)

        End With
    End Sub
    Public Sub LoadDataToLVUsers(ByVal SQL As String, ByVal LV As ListView)
        Dim cn As New OleDbConnection
        Dim DA As OleDbDataAdapter
        Dim DS As New DataSet
        ' Dim dt As DataTable
        Dim dbCmd As OleDbCommand
        cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\students.accdb"
        cn.Open()
        dbCmd = New OleDbCommand(SQL, cn)
        DA = New OleDbDataAdapter(dbCmd)
        DS = New DataSet
        DA.Fill(DS)
        LV.Clear()
        Dim j As Integer

        For i = 0 To DS.Tables(0).Rows.Count - 1
            With LV
                .Items.Add(DS.Tables(0).Rows(i)("UserID").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("UserName").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("UserLogin").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("Gender").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("Group").ToString)
                .Items(j).SubItems.Add(DS.Tables(0).Rows(i)("ImageLink").ToString)
            End With
            j = j + 1
        Next
        cn.Close()
        With LV
            .View = View.Details
            .GridLines = True
            .FullRowSelect = True
            .Columns.Add("លេខរៀង", 100, HorizontalAlignment.Center)
            .Columns.Add("ឈ្មោះ​អ្នក​ប្រើ​​​", 150, HorizontalAlignment.Center)
            .Columns.Add("​ឈ្មោះLogin", 0, HorizontalAlignment.Center)
            .Columns.Add("ភេទ​", 0, HorizontalAlignment.Center)
            .Columns.Add("ក្រុម​​", 0, HorizontalAlignment.Center)
            .Columns.Add("រូបភាព​", 0, HorizontalAlignment.Center)

        End With
    End Sub
    Public Sub DeleteDataAccess(ByVal SQLStr As String)
        Try
            Dim cn As New OleDb.OleDbConnection
            cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\students.accdb"
            cn.Open()
            Dim myCmd As New OleDb.OleDbCommand
            myCmd.Connection = cn
            myCmd.CommandText = SQLStr
            myCmd.ExecuteNonQuery()
            cn.Close()
            MsgBox("Data Delete successfully!")
        Catch ex As Exception
            MsgBox(ex, MsgBoxStyle.Information, "Students Managment System Error")
        End Try
    End Sub
    Public Sub SaveDataToAccess(ByVal SQLStr As String)
        Try
            Dim cn As New OleDb.OleDbConnection
            cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\students.accdb"
            cn.Open()
            Dim myCmd As New OleDb.OleDbCommand
            myCmd.Connection = cn
            myCmd.CommandText = SQLStr
            myCmd.ExecuteNonQuery()
            cn.Close()
            MsgBox("Data Save successfully!")
        Catch ex As Exception
            MsgBox(ex, MsgBoxStyle.Information, "Student Management System Error")
        End Try
    End Sub
End Module
